package db;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;

import db.sql.AndWhere;

import model.Enterprise;
import model.type.SubjectType;
import search.SearchAttribute;
import search.SearchString;

public class EnterpriseDAO {
	protected static final String TABLE = "enterprise";	
	protected static final String SEARCH_TABLE = "enterprise_details";
	private static String onSqlError = "SQL statement fault";
		
	
	private static final String 
		ID_FIELD = "enterprise",	
		NAME_FIELD = "name",
		FULL_NAME_FIELD = "full_name",
		CREATED_BY_FIELD = "created_by",
		UPDATED_BY_FIELD = "updated_by",
		CREATED_FIELD = "created",
		UPDATED_FIELD = "updated";
	
	private static final String INSERT_STATEMENT_STRING = 
			"INSERT INTO "+TABLE+" " +
			"("+NAME_FIELD+", "+FULL_NAME_FIELD+", " +CREATED_BY_FIELD+", "+UPDATED_BY_FIELD+", "+
			CREATED_FIELD+", "+UPDATED_FIELD+") " +
			"VALUES (?, ?, ?, ?, ?, ?)";
	
	private static final String UPDATE_STATEMENT_STRING = 
			"UPDATE "+TABLE+" SET " 
			+NAME_FIELD+" = ?, "+FULL_NAME_FIELD+" = ?, "+CREATED_BY_FIELD+" = ?, "
			+UPDATED_BY_FIELD+" = ?, "+CREATED_FIELD+" = ?, "+UPDATED_FIELD+" = ? " 
			+"WHERE "+ID_FIELD+" = ?";
	
	private static final String DELETE_STATEMENT_STRING =
			"DELETE FROM "+TABLE+" WHERE "+ID_FIELD+" = ?";
	
	private static PreparedStatement insertStatement, updateStatement, deleteStatement;
	
	static{
		try {
			insertStatement = ImbiConnection.getConnection().prepareStatement(INSERT_STATEMENT_STRING);
			updateStatement = ImbiConnection.getConnection().prepareStatement(UPDATE_STATEMENT_STRING);
			deleteStatement = ImbiConnection.getConnection().prepareStatement(DELETE_STATEMENT_STRING);
		} catch (SQLException e) {					
			System.exit(0);
		}
	}
	
	public Enterprise find(int id) {			
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" = "+id);						
			return extractEnterprise(st.getResultSet()).get(0);
		} catch (SQLException | IndexOutOfBoundsException s){
				System.out.println(onSqlError);				
				return null;
		}		
	}

	private ArrayList<Enterprise> extractEnterprise(ResultSet r) throws SQLException {
		ArrayList<Enterprise> list = new ArrayList<Enterprise>();
		while(r.next()){	
			Date created = r.getTimestamp(6) != null ? new Date(r.getTimestamp(6).getTime()) : null;
			Date updated = r.getTimestamp(7) != null ? new Date(r.getTimestamp(7).getTime()) : null;
			
			Enterprise e = new Enterprise(
				r.getInt(1),
				r.getString(2),
				r.getString(3),
				r.getInt(4),
				r.getInt(5),
				created,
				updated				
			);
			list.add(e);
		}
		return list;
	}	
	
	public ArrayList<Enterprise> findAll() {
		try{				
			Statement st = ImbiConnection.getConnection().createStatement();
			st.executeQuery("SELECT * FROM "+TABLE);								
			return extractEnterprise(st.getResultSet());			
		} catch (SQLException s){
				System.out.println(onSqlError);				
				return null;
		}		
		
	}
	
	public void insert(Enterprise e){
		try{
			Timestamp created = 
					e.getCreated() != null ? new Timestamp(e.getCreated().getTime()) : null;
			Timestamp updated = 
					e.getUpdated() != null ? new Timestamp(e.getUpdated().getTime()) : null;
		    insertStatement.setString(1, e.getName());
		    insertStatement.setString(2, e.getFullName());		    
		    insertStatement.setInt(3, e.getCreatedBy());
		    insertStatement.setInt(4, e.getUpdatedBy());
		    insertStatement.setTimestamp(5, created);		
		    insertStatement.setTimestamp(6, updated);		
		    insertStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);								
		}		
	}
	
	public void update(Enterprise e){
		try{										
			Timestamp created = 
					e.getCreated() != null ? new Timestamp(e.getCreated().getTime()) : null;
			Timestamp updated = 
					e.getUpdated() != null ? new Timestamp(e.getUpdated().getTime()) : null;
		    updateStatement.setString(1, e.getName());
		    updateStatement.setString(2, e.getFullName());		    
		    updateStatement.setInt(3, e.getCreatedBy());
		    updateStatement.setInt(4, e.getUpdatedBy());
		    updateStatement.setTimestamp(5, created);		
		    updateStatement.setTimestamp(6, updated);	       
		    updateStatement.setInt(7, e.getId());
		    updateStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);									
		}			
	}
	
	public void delete(Enterprise e){
		try{										
		    deleteStatement.setInt(1, e.getId());		    
		    deleteStatement.executeUpdate();			
		} catch (SQLException s){
				System.out.println(onSqlError);						
		}			
	}	
	
	public ArrayList<Enterprise> search(SearchString search){
		AndWhere where = new AndWhere();
		try{				
			
			String subquery = "SELECT "+ID_FIELD+" FROM "+SEARCH_TABLE;
			if(search.getType() == SubjectType.CUSTOMER){
				subquery+=" "+where.next()+" customer IS NOT NULL";
			}
			
			if(search.getCountry() != null){
				subquery+=" "+where.next()+" lower(country) LIKE lower(?)"; 
			}
			
			if(search.getAddress() != null){
				subquery+=" "+where.next()+" lower(street_address) LIKE lower(?)";
			}
			
			if(search.getCity() != null){
				subquery+=" "+where.next()+" lower(town_village) LIKE lower(?)";
			}
			
			if(search.getCounty() != null){
				subquery+=" "+where.next()+" lower(county) LIKE lower(?)";
			}
			
			if(search.getZip() != null){
				subquery+=" "+where.next()+" lower(zipcode) LIKE lower(?)";
			}
			
			if(search.getContact() != null){
				subquery+=" "+where.next()+" lower(contact_value) LIKE lower(?)";
			}
			
			if(search.getName() != null){
				subquery+=" "+where.next()+" ( lower(name) LIKE lower(?) OR  lower(full_name) LIKE lower(?))";
			}			
			
			int atrsize = search.getAttributes().size();
			if(atrsize > 0) subquery+=" "+where.next()+" (";
			for(int i = 0; i< atrsize; i++){
				subquery+=" "+(i == 0 ? "" : "OR")+" subject_attribute_type_fk = ? AND lower(value) LIKE lower(?)";
			}
			if(atrsize > 0) subquery+=" )";
			
			String query = "SELECT * FROM "+TABLE+" WHERE "+ID_FIELD+" IN ("+subquery+")";
			PreparedStatement st = ImbiConnection.getConnection().prepareStatement(query);
			int count = 1;
			
			if(search.getCountry() != null) st.setString(count++, "%"+search.getCountry()+"%");
			if(search.getAddress() != null) st.setString(count++, "%"+search.getAddress()+"%");
			if(search.getCity() != null) st.setString(count++, "%"+search.getCity()+"%");
			if(search.getCounty() != null) st.setString(count++, "%"+search.getCounty()+"%");
			if(search.getZip() != null) st.setString(count++, "%"+search.getZip()+"%");
			if(search.getContact() != null) st.setString(count++, "%"+search.getContact()+"%");
			if(search.getName() != null){ 
				st.setString(count++, "%"+search.getName()+"%"); // name
				st.setString(count++, "%"+search.getName()+"%"); // fullname
			}
			
			if(search.getAttributes().size() > 0){
				for(SearchAttribute atr: search.getAttributes()){
					st.setInt(count++, atr.getType());
					st.setString(count++, "%"+atr.getValue()+"%");
				}
			}
			
			st.executeQuery();	
			return extractEnterprise(st.getResultSet());
		} catch (SQLException | IndexOutOfBoundsException s){
				System.out.println(onSqlError);				
				return null;
		}	
	}
}
